package com.byxm.wlgw.query;

import com.byxm.wlgw.bean.KcxxBean;
import com.byxm.wlgw.bean.SpxxBean;
import com.byxm.wlgw.bean.SpxxpxBean;
import com.byxm.wlgw.common.ComUtil;
import com.byxm.wlgw.common.Pages;

public class SpxxQuery extends BaseQuery{
	public SpxxQuery(String sTableNm) {
		super(sTableNm);
	}
	/**
	 * 获取分页信息的
	 * @param spxx
	 * @param pages
	 * @return select * from (select top 12 * from (selet distinct top(12*1) t1.* from spxx t1,kcxx t2 where t1.spxxid=t2.spxxid and t1.splbfid=1 order by xxxx asc)as spxxtba order by xxx desc)as spxxtb order by asc
	 */
	public String getSpxxSql(SpxxBean spxx,Pages pages){
		StringBuffer sql = new StringBuffer();
		sql.append("select * from (");
		sql.append("select top "+pages.getPageNum()+" * from (");
		sql.append(" select DISTINCT top (12*").append(pages.getCpage()).append(")");//当前页数
		sql.append(" t1.* ");
		sql.append("from spxx t1,kcxx t2 where t1.spxxid=t2.spxxid ");
		sql.append(" and t1.splbfid=").append(spxx.getSplbfid());
		if(spxx.getSplbsid()!=0) {
	    	sql.append(" and t1.splbsid=").append(spxx.getSplbsid());
      	}
		if(spxx.getPpid()!=0) {
			sql.append(" and t1.ppid=").append(spxx.getPpid());
		}
		if(spxx.getJgqjid()!=0) {
			sql.append(" and t1.jgqjid=").append(spxx.getJgqjid());
		}
		if(spxx.getXhid()!=0) {
			sql.append(" and t2.xhid=").append(spxx.getXhid());
		}
		
		sql.append(" order by t1.spxxid asc)");
		sql.append("as spxxtba order by spxxid desc)");
		sql.append(" as spxxtbb order by spxxid asc");
		return sql.toString();
	}
	/**
	 * 获取商品总数的Sql
	 * @param spxx
	 * @return
	 */
	public String getSpxxCnt(SpxxBean spxx){
		StringBuffer sql = new StringBuffer();
		sql.append("select count(DISTINCT t1.spxxid)as ct from spxx t1 ");
		/**库存***/
		sql.append(this.getKcxxTbSql(spxx.getKczt()));
		/***条件*****/
		if(spxx.getSplbfid()!=0){
			sql.append(" and t1.splbfid=").append(spxx.getSplbfid());
		}
		if(spxx.getSplbsid()!=0) {
	    	sql.append(" and t1.splbsid=").append(spxx.getSplbsid());
      	}
		if(spxx.getPpid()!=0) {
			sql.append(" and t1.ppid=").append(spxx.getPpid());
		}
		if(spxx.getJgqjid()!=0) {
			sql.append(" and t1.jgqjid=").append(spxx.getJgqjid());
		}
		if(spxx.getXhid()!=0) {
			sql.append(" and t2.xhid=").append(spxx.getXhid());
		}

		return sql.toString();
	}
	
	/**
	 * 根据spxxid(商品信息ID获取单条商品记录)
	 * @param spxxid
	 * @return
	 */
	public String getSpxxBySpxxidSql(int spxxid){
		StringBuffer sql = new StringBuffer();
		sql.append("select t1.*,t2.spppm,t3.splbm as spks from spxx t1 left join sppp t2 on t1.ppid=t2.spppid ");
		sql.append(" left join splb_s t3 on t1.splbsid=t3.splbsid ");
		sql.append(" where t1.spxxid=").append(spxxid);
		return sql.toString();
	}
	
	/**
	 * 保存商品信息
	 */
	public String getSaveSpxxSql(){
		StringBuffer sql = new StringBuffer();
		sql.append("insert into spxx(sphh,spbt,spms,spyj,spsj,fbrq,ppid,splbfid,splbsid,sptp,jgqjid,jhjg,spzstp,kcsl)values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
		return sql.toString();
	}
	/**
	 * 查找商品信息,用批处理   ,kcxx t2 where t1.spxxid=t2.spxxid and t1.splbfid=? 
	 */
	public String findSpxxSql(SpxxBean spxxBean,Pages page){//有库存,
		StringBuffer sql=new StringBuffer();
		sql.append("select * from");
		sql.append(" (");
		sql.append("select top ").append(page.getPageNum());
		sql.append("* from");
		sql.append(" (");
		sql.append("select distinct top(").append(page.getPageNumFin()).append("*").append(page.getCpage()).append(")");
		sql.append(" t1.* from spxx t1");
		//库存状态
		sql.append(this.getKcxxTbSql(spxxBean.getKczt()));
		//各种类别
		if(this.xySpxxBean(spxxBean)){
			
				if(spxxBean.getSplbfid()!=0){//商品大类别
					sql.append(" and t1.splbfid=").append(spxxBean.getSplbfid());
				}
				if(spxxBean.getSplbsid()!=0){//商品功能
					sql.append(" and t1.splbsid=").append(spxxBean.getSplbsid());
				}
				if(spxxBean.getPpid()!=0){//商品品牌ID
					sql.append(" and t1.ppid=").append(spxxBean.getPpid());
				}
				if(spxxBean.getJgqjid()!=0){//商品价区
					sql.append(" and t1.jgqjid=").append(spxxBean.getJgqjid());
				}
				if(spxxBean.getXhid()!=0&&spxxBean.getKczt()==1){//型号
					sql.append(" and t2.xhid=").append(spxxBean.getXhid());
				}
				if(!ComUtil.isNull(spxxBean.getSearchString())){//搜索字符串
					sql.append(" and spbt like ").append("'%").append(spxxBean.getSearchString()).append("%'");
					sql.append(" or sphh like ").append("'%").append(spxxBean.getSearchString()).append("%'");
				}
			
		}
		//商品排序
		SpxxpxBean spxxpxBean=spxxBean.getSpxxpx();//获得排序对象
		sql.append(" order by ").append(spxxpxBean.getPxzd()).append(" asc");
		sql.append(")as spxxtba order by ").append(spxxpxBean.getPxzd()).append(" desc");
		sql.append(")as spxxtbb order by ").append(spxxpxBean.getPxzd()).append(" ").append(spxxpxBean.getSqlgjz());
		//1：问号是当前显示多少页数,2：当前第几页3:条件语句: ,kcxx t2 4.条件语句:t1.spxxid=t2.spxxid 5:and t1.splbfid=xx..and .. 
		//sql.append("select distinct top(?*?) t1.* from spxx t1 ? where 1=1 ? ? ");
		//sql.append("order by ? asc)as spxxtba order by ? desc)as spxxtbb order by ? ?");
		//排序col
		return sql.toString();
	}
	
	/**
	 * 更新库存
	 */
	public String updateKcxxSql(){
		StringBuffer sql=new StringBuffer();
		sql.append("update spxx set kcsl=kcsl+? where spxxid=?");
		return sql.toString();
	}
	
	/******私有方法**********/
	private String getKcxxTbSql(int kczt){
		StringBuffer sql=new StringBuffer();
		if(kczt==0){
			sql.append(" where 1=1");
		}
		if(kczt==1){//有库存
			sql.append(",kcxx t2 where");
			sql.append(" t1.spxxid=t2.spxxid ");
		}
		if(kczt==2){//没有库存 where t1.spxxid not in(select spxxid from kcxx) 
			sql.append(" where t1.spxxid not in (select spxxid from kcxx)");
		}
		return sql.toString();
	}
	
	private boolean xySpxxBean(SpxxBean bean){
		if(bean.getSplbfid()!=0||bean.getSplbsid()!=0||bean.getPpid()!=0||bean.getJgqjid()!=0||bean.getXhid()!=0||bean.getSearchString().trim()!=""){
			return true;
		}else{
			return false;
		}
	}
	
		
	
}
